package table;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import database.DatabaseConnection;

/**
 * 处理小组相关的所有数据库操作的类
 */
public class TeamHandle {
	Connection conn;
	Statement stmt;
	ResultSet rs;
	
	/**
	 * 初始化连接
	 */
	private void initConnection() {
		try {
			long t1 = System.currentTimeMillis();
			conn = DatabaseConnection.getConnection();
			stmt = conn.createStatement();
			long last = System.currentTimeMillis() - t1;
			System.out.println("建立连接耗时: " + last + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public TeamHandle() {
		System.out.println("####TeamHandle创建####");
		initConnection();
	}
	
	@Override
	protected void finalize() throws Throwable {
		try {
			System.out.println("####TeamHandle销毁####");
			conn.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		super.finalize();
	}
	
	/**
	 * 通过teamid获取小组
	 * @param teamid
	 * @return
	 */
	public Team getTeamById(int teamid) {
		Team team = new Team();
		team.setId(teamid);
		
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_team WHERE team_id='" + teamid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				team.setName(rs.getString("team_name"));
				team.setInfo(rs.getString("team_info"));
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return team;
	}
	
	/**
	 * 获取所有小组
	 * @return 包含所有小组的List
	 */
	public List<Team> getAllTeams(){
		ArrayList<Team> list = new ArrayList<>();
		
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_team";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				Team team = new Team();
				team.setId(rs.getInt("team_id"));
				team.setName(rs.getString("team_name"));
				team.setInfo(rs.getString("team_info"));
				list.add(team);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取id为teamid小组的所有成员信息
	 * @return 包含所有成员的List
	 */
	public List<User> getUsersFromTeam(int teamid){
		ArrayList<User> list = new ArrayList<>();
		
		try {
			if(conn == null) initConnection();
			String sql = "SELECT a.user_id, a.user_name, a.user_teamid, a.user_group, b.team_name FROM tb_user as a LEFT JOIN tb_team as b ON a.user_teamid=b.team_id WHERE user_teamid=" + teamid;
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				User user = new User();
				user.setId(rs.getString("user_id"));
				user.setName(rs.getString("user_name"));
				user.setTeamid(rs.getInt("user_teamid"));
				user.setTeamName(rs.getString("team_name"));
				user.setGroup(rs.getString("user_group"));
				list.add(user);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取teamid中包含idScreen字符串的所有Team对象
	 * @param idScreen
	 * @return Team列表
	 */
	public List<Team> getTeamsWithIdLike(String idScreen){
		ArrayList<Team> list = new ArrayList<>();
		
		try {
			if(conn == null) initConnection();
			String sql = 
			"SELECT * FROM tb_team WHERE team_id like '%"+ idScreen + "%';";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				Team t = new Team();
				t.setId(rs.getInt("team_id"));
				t.setName(rs.getString("team_name"));
				t.setInfo(rs.getString("team_info"));
				list.add(t);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**

	 * 将team的数据更新到数据库，若修改了小组编号，则会更新所有成员对应的小组编号
	 * @param old_teamid 旧的teamid
	 * @param team 要更新成的team数据
	 * @return 成功返回true，失败返回false
	 */
	public boolean updateToDB(int old_teamid ,Team team) {
		boolean ret = false;
		String id = String.valueOf(team.getId());
		String name = team.getName();
		String info = team.getInfo();
		
		try {
			if(conn == null) initConnection();
			String sql = String.format(
					"UPDATE tb_team SET team_id=\"%s\", team_name=\"%s\", " +
					"team_info=\"%s\"",id, name, info);
			sql += " WHERE team_id=\"" + old_teamid + "\";";	
			System.out.println(sql);
			int result = stmt.executeUpdate(sql);
			if(result != 0) {
				if(old_teamid != 0) {
					sql = String.format("UPDATE tb_user SET user_teamid='%s' WHERE user_teamid='%d';", id, old_teamid);
					result = stmt.executeUpdate(sql);
				}
				ret = true;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 通过小组id删除指定小组，并将所有小组成员的小组id更新为0（无小组）
	 * @param teamid
	 * @return 成功返回true，失败返回false
	 */
	public boolean deleteTeamById(int teamid) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_team WHERE team_id = '" + teamid + "'";
			int result = stmt.executeUpdate(sql);
			if(result != 0) {
				if(teamid != 0) {
					sql = String.format("UPDATE tb_user SET user_teamid='0' WHERE user_teamid='%d';", teamid);
					result = stmt.executeUpdate(sql);
				}
				ret = true;
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 添加新小组进数据库
	 * @param team 新的小组
	 * @return 成功返回true，失败返回false
	 */
	public boolean addTeam(Team team) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = String.format("INSERT tb_team VALUES('%d', '%s', '%s');", team.getId(), team.getName(), team.getInfo());
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
}
